/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package cz.tpj.database.bod_zaujmu;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import cz.tpj.database.DatabaseConnector;
import cz.tpj.database.aktivita_zajimavost.TableActivityInterests;
import cz.tpj.database.mesto.Table_city;
import cz.tpj.database.obrazky.Obrazok;
import cz.tpj.database.typ_zaujimavost.Typ_zaujimavost;
import cz.tpj.util.ErrorDatabase;



/**
 * Trieda sprostredkuvava metody pre pridavanie, odoberanie, editovanie a vyberanie
 * Bodov zaujmu z databazy
 * @author jabo
 */

public class Table_Bod_zaujmu {
    //------------------finalni promenne----------------------------------------
    public static String TABLE          = "bod_zaujmu";
    public static String ID             = "id";
    public static String NAME           = "nazov";
    public static String SHORT_DESC     = "kratky_popis";
    public static String LONG_DESC      = "dlhy_popis";
    public static String ADD_TIME       = "pridane";
    public static String LAST_EDIT_TIME = "naposledy_editovane";
    public static String CITY_ID        = "mesto_id";
    public static String EMPLOYEE_ID    = "zamestnanec_id";
    public static String GPS			= "gps";
    //-------------------------------------------------------------------------
    private static Table_Bod_zaujmu im = null;
    private Connection con;
    private Statement stmt = null;
    //-------------------------------------------------------------------------
    public static Table_Bod_zaujmu getInstance(){
        if(im == null){
            im = new Table_Bod_zaujmu();
        }
        return im;
    }
    //=======================metody-------------------------
    private void databaseConnect() throws SQLException{
        DatabaseConnector dc = DatabaseConnector.getInstance();
        con = dc.getConnection();
        stmt = con.createStatement();
    }

    //--------------------------------------------------------------------------
    
    
    
    public ArrayList<Bod_zaujmu> getAktivita_Body(String[] aktivity) throws ErrorDatabase {
        ResultSet rs = null;
        ArrayList<Bod_zaujmu> score = new ArrayList<Bod_zaujmu>();
        
        try {
            databaseConnect();
            
            //vybuduj podmienku podla zadaneho pola aktivit
            String podmienka = "";
            for (int i = 0; i < aktivity.length; i++) {
            	if (i < (aktivity.length - 1)) {
            		podmienka = podmienka + "b.id = " + aktivity[i] + " OR ";
            	} else {
            		podmienka = podmienka + "b.id = " + aktivity[i];
            	}
            }
            
            String query = "SELECT a.id, a.nazov, a.kratky_popis, a.dlhy_popis, g.meno" +
            " FROM bod_zaujmu a" +
            " LEFT JOIN (aktivity b, aktivita_zaujimavost c, zamestnanec f, mesto g" +                    
            ") ON (a.id = c.bod_zaujmu_id AND c.aktivity_id = b.id AND a.zamestnanec_id = f.id AND a.mesto_id = g.id ) " +
            " WHERE " + podmienka +
            " GROUP BY a.id ORDER BY a.nazov";
            rs = stmt.executeQuery(query);
            while(rs.next()){
            	Bod_zaujmu temp = new Bod_zaujmu();
            	temp.setId(rs.getString(ID));
            	temp.setNazov(rs.getString(NAME));
                temp.setKratkyPopis(rs.getString(SHORT_DESC));
                temp.setDlhyPopis(rs.getString(LONG_DESC));
                //String mesto_meno = rs.getString(Table_city.NAME);
                
                /*
                ResultSet rs_sub = null;
                rs_sub = stmt.executeQuery("SELECT a.id, a.cesta " +
                        " FROM obrazky a " +
                        " LEFT JOIN (obrazok_zaujimavost b, bod_zaujmu c) ON " +
                        " (a.id = b.obrazky_id AND b.bod_zaujmu_id = c.id)" +
                        " WHERE c.id = " + id
                );
                Obrazok obrazky = new Obrazok();
                while (rs_sub.next()) {
                    String o_id = rs_sub.getString(1);
                    String o_cesta = rs_sub.getString(2);
                    obrazky = new Obrazok(o_id, o_cesta);
                }

                rs_sub = null;
                rs_sub = stmt.executeQuery("SELECT a.id, a.meno " +
                        " FROM typy_zaujimavosti a " +
                        " LEFT JOIN (map_typ_zaujimavost b, bod_zaujmu c) ON " +
                        " (a.id = b.typy_zaujimavosti_id AND b.bod_zaujmu_id = c.id)" +
                        " WHERE c.id = " + id
                );
                Typ_zaujimavost zaujimavost = new Typ_zaujimavost();
                while (rs_sub.next()) {
                    String o_id = rs_sub.getString(1);
                    String o_cesta = rs_sub.getString(2);
                    zaujimavost = new Typ_zaujimavost(o_id, o_cesta);
                }
                */
                //Bod_zaujmu temp = new Bod_zaujmu (id, nazov, kratky_popis, dlhy_popis, pridane, naposledy_editovane, login, mesto_meno, zaujimavost, obrazky);
                score.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Table_Bod_zaujmu.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            try {
                rs.close();
                stmt.close();
            } catch (Exception ex) {
            }

        }
        
        
    	return score; 
    }
    
    
    public ArrayList<Bod_zaujmu> getAllInterests() throws ErrorDatabase{
    	ResultSet rs = null;
        ArrayList<Bod_zaujmu> score = new ArrayList<Bod_zaujmu>();
        try {
            databaseConnect();
            //SELECT * FROM bod_zaujmu WHERE id = 1;
            rs = stmt.executeQuery("SELECT * FROM " + TABLE + ";");
            
            String s;
            while(rs.next()){
                Bod_zaujmu temp = new Bod_zaujmu();
                s = rs.getString(ID);
                temp.setId(s);
                s = rs.getString(NAME);
                temp.setNazov(s);
                s = rs.getString(SHORT_DESC);
                temp.setKratkyPopis(s);
                s = rs.getString(LONG_DESC);
                temp.setDlhyPopis(s);
                s = rs.getString(ADD_TIME);
                temp.setPridane(s);
                s = rs.getString(LAST_EDIT_TIME);
                temp.setNaposledyEditovane(s);
                s = rs.getString(CITY_ID);
                temp.setMesto(s);
                s = rs.getString(EMPLOYEE_ID);
                temp.setZamestnanec(s);
                score.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Table_city.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            try {
            	con.commit();
                rs.close();
                stmt.close();
            } catch (Exception ex) {
            }
        }
        return score;
    }
    
    /**
     * vyberie bod zaujmu, ktory vyhovuje zadanemu id 
     * @param idCity
     * @return ArrayList<Bod_zaujmu>
     * @throws cz.tpj.util.ErrorDatabase
     */
    
    public ArrayList<Bod_zaujmu> getCulturalCityInterests(String idCity) throws ErrorDatabase {
        ResultSet rs = null;
        ArrayList<Bod_zaujmu> score = new ArrayList<Bod_zaujmu>();
        try {
            databaseConnect();
            //SELECT * FROM bod_zaujmu WHERE id = 1;
            rs = stmt.executeQuery("SELECT * FROM "+TABLE+" WHERE "+CITY_ID+" = "+idCity+";");
            String a = "SELECT * FROM "+TABLE+" WHERE "+ID+" = "+idCity+";";
            String s;
            while(rs.next()){
                Bod_zaujmu temp = new Bod_zaujmu();
                s = rs.getString(ID);
                temp.setId(s);
                s = rs.getString(NAME);
                temp.setNazov(s);
                s = rs.getString(SHORT_DESC);
                temp.setKratkyPopis(s);
                s = rs.getString(LONG_DESC);
                temp.setDlhyPopis(s);
                s = rs.getString(ADD_TIME);
                temp.setPridane(s);
                s = rs.getString(LAST_EDIT_TIME);
                temp.setNaposledyEditovane(s);
                s = rs.getString(CITY_ID);
                temp.setMesto(s);
                s = rs.getString(EMPLOYEE_ID);
                temp.setZamestnanec(s);
                score.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Table_city.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            try {
                rs.close();
                stmt.close();
            } catch (Exception ex) {
            }
        }
        return score;
    }

    /**
     * vyberie pole bodov zaujmu, ktore vyhovuju zadanej podmienke. 
     * Podmienka je sucast sql dotazu, konkretne jej cast where
     * @param podmienka
     * @return ArrayList<Bod_zaujmu>
     * @throws cz.tpj.util.ErrorDatabase
     */
    
    public ArrayList<Bod_zaujmu> getCondBody(String podmienka) throws ErrorDatabase {
        ResultSet rs = null;
        ArrayList<Bod_zaujmu> score = new ArrayList<Bod_zaujmu>();
        try {
            databaseConnect();
            rs = stmt.executeQuery("SELECT a.id, a.nazov, a.kratky_popis, a.dlhy_popis, a.pridane, a.naposledy_editovane, f.login, g.meno" +
                    " FROM bod_zaujmu a" +
                    " LEFT JOIN (zamestnanec f, mesto g" +
                    ") ON (a.zamestnanec_id = f.id AND a.mesto_id = g.id )" +
                    " WHERE 1" + podmienka +
                    " GROUP BY a.id ORDER BY a.nazov"
            );
            String s;
            while(rs.next()){
                String id = rs.getString(1);
                String nazov = rs.getString(2);
                String kratky_popis = rs.getString(3);
                String dlhy_popis = rs.getString(4);
                String pridane = rs.getString(5);
                String naposledy_editovane = rs.getString(6);
                String login = rs.getString(7);
                String mesto_meno = rs.getString(8);

                ResultSet rs_sub = null;
                rs_sub = stmt.executeQuery("SELECT a.id, a.cesta " +
                        " FROM obrazky a " +
                        " LEFT JOIN (obrazok_zaujimavost b, bod_zaujmu c) ON " +
                        " (a.id = b.obrazky_id AND b.bod_zaujmu_id = c.id)" +
                        " WHERE c.id = " + id
                );
                Obrazok obrazky = new Obrazok();
                while (rs_sub.next()) {
                    String o_id = rs_sub.getString(1);
                    String o_cesta = rs_sub.getString(2);
                    obrazky = new Obrazok(o_id, o_cesta);
                }

                rs_sub = null;
                rs_sub = stmt.executeQuery("SELECT a.id, a.meno " +
                        " FROM typy_zaujimavosti a " +
                        " LEFT JOIN (map_typ_zaujimavost b, bod_zaujmu c) ON " +
                        " (a.id = b.typy_zaujimavosti_id AND b.bod_zaujmu_id = c.id)" +
                        " WHERE c.id = " + id
                );
                Typ_zaujimavost zaujimavost = new Typ_zaujimavost();
                while (rs_sub.next()) {
                    String o_id = rs_sub.getString(1);
                    String o_cesta = rs_sub.getString(2);
                    zaujimavost = new Typ_zaujimavost(o_id, o_cesta);
                }
                Bod_zaujmu temp = new Bod_zaujmu (id, nazov, kratky_popis, dlhy_popis, pridane, naposledy_editovane, login, mesto_meno, zaujimavost, obrazky);
                score.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Table_Bod_zaujmu.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            try {
                rs.close();
                stmt.close();
            } catch (Exception ex) {
            }

        }
        return score;
    }
    
    /**
     * vlozi do databazy bod zaujmu, ktory je v parametri vkladane. 
     * Vrati true ak je operacia uspesna, opacne vrati false.
     * @param vkladane
     * @return boolean
     * @throws cz.tpj.util.ErrorDatabase
     */
    public boolean insertBodZaujmu(Bod_zaujmu vkladane) throws ErrorDatabase {
        //pokud true pak transakce probehla dobre, jinak ...
        boolean ok = false;
        //nahradí znaky kvuli pozdejsi interpretaci prohizece (jedna se o uvozovky)
  		String gps = vkladane.getGps();
  		if(gps != null){
  			gps = gps.replace("'","%27");
  	  		gps = gps.replace("\"","%22");
  		}
        try {
            databaseConnect();
            //zkontroluj, jestli nazev mista v databazi jiz neexistuje
            //SELECT id FROM bod_zaujmu WHERE nazov='aaa';
            ResultSet rs = null;
            String s = "SELECT " + ID + " FROM " + TABLE + " WHERE " + NAME + "='" + vkladane.getNazov() + "';";
            rs = stmt.executeQuery(s);
            //jestlize uz existuje pak skonci a vrat false
            if(rs.next()){
            	ok = false;
            }else{
	            s = "INSERT INTO " + TABLE +
	                    "(" + NAME + "," + SHORT_DESC + "," + LONG_DESC + "," + ADD_TIME + "," + LAST_EDIT_TIME + "," + CITY_ID + "," + EMPLOYEE_ID + "," + GPS +")" +
	                    "VALUES ('" + vkladane.getNazov() + "','" + vkladane.getKratkyPopis() + "','"
	                    + vkladane.getDlhyPopis() + "','" + vkladane.getPridane() + "','" + vkladane.getNaposledyEditovane() + "','" + vkladane.getMesto() + "','" + vkladane.getZamestnanec() + "','" + gps +"')";
	            
	            int updated = stmt.executeUpdate(s);
	            //pokud
	            //1) byl zmenen jeden radek, pak potvrzeni transakce
	            //2) bylo zmeneno vice a nebo zadny, pak vratit transakci zpet
	            if(updated == 1){
	                con.commit(); //potvrzeni transakce
	                ok = true;
	            }else{
	                con.rollback(); //vraceni transakce
	                ok = false;
	            }
            }
        } catch (SQLException ex) {
            throw new ErrorDatabase(ErrorDatabase.ERROR_DATABASE_CONECTION, "Cbyba pri praci s databazovym serverem." + ex.getMessage());
        } finally{
            try {
                stmt.close();
            } catch (SQLException ex) {}
        }
        return ok;
    }
    
    public String getLastInsertedIterestPlace() throws ErrorDatabase {
        ResultSet rs = null;
        String cityId = null;
        try {
            databaseConnect();
            rs = stmt.executeQuery("SELECT * FROM "+TABLE+" ORDER BY id DESC LIMIT 0,1;");

            while(rs.next()){
                //nacteni
                cityId = rs.getString(ID);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Table_city.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            try {
                rs.close();
                stmt.close();
            } catch (Exception ex) {
            }
        }
        return cityId;
    }
    
    
    /**
     * Zmaze bod zaujmu ktory ma rovnake id ako je specifikovane parametrom
     * Vrati true ak je operacia uspesna, opacne vrati false.
     * @param id
     * @return boolean
     * @throws cz.tpj.util.ErrorDatabase
     */
    
    public boolean deleteBodZaujmu(int id) throws ErrorDatabase {
        //pokud true pak transakce probehla dobre, jinak ...
        boolean ok = false;
        try {
            databaseConnect();
          //smaz i zaznamy od aktivitach
            TableActivityInterests.getInstance().deleteActivitiesIfDeletedInterest(String.valueOf(id));
            
            String s = "DELETE FROM " + TABLE + " WHERE " + ID + " = " + id;
            int updated = stmt.executeUpdate(s);
            
            //pokud
            //1) byl zmenen jeden radek, pak potvrzeni transakce
            //2) bylo zmeneno vice a nebo zadny, pak vratit transakci zpet
            if(updated == 1){
                con.commit(); //potvrzeni transakce
                ok = true;
            }else{
                con.rollback(); //vraceni transakce
                ok = false;
            }
        } catch (SQLException ex) {
            throw new ErrorDatabase(ErrorDatabase.ERROR_DATABASE_CONECTION, "Cbyba pri praci s databazovym serverem.");
        } finally{
            try {
                stmt.close();
            } catch (SQLException ex) {}
        }
        return ok;
    }
    
    /**
     * Aktualizuje bod zaujmu, ktory ma rovnake id ako id ktore nesie bod 
     * zaujmu zadany v parametri. Vrati true v pripade uspesnej transakcie, 
     * inak vrati false.
     * @param upravovane
     * @return boolean
     * @throws cz.tpj.util.ErrorDatabase
     */
    
    public boolean updateBodZaujmu(Bod_zaujmu upravovane) throws ErrorDatabase {
        //pokud true pak transakce probehla dobre, jinak ...
        boolean ok = false;
        try {
            databaseConnect();
            String s = "UPDATE " + TABLE + " SET " + NAME + " = '" + upravovane.getNazov() + "', " + 
                    SHORT_DESC + " = '" + upravovane.getKratkyPopis() + "', " + 
                    LONG_DESC + " = '" + upravovane.getDlhyPopis() + "', " + 
                    ADD_TIME + " = '" + upravovane.getPridane() + "', " + 
                    CITY_ID + " = '" + upravovane.getMesto() + "', " + 
                    LAST_EDIT_TIME + " = '" + upravovane.getNaposledyEditovane() + "', " + 
                    EMPLOYEE_ID + " = '" + upravovane.getZamestnanec() + "'";
                        
            int updated = stmt.executeUpdate(s);
            //pokud
            //1) byl zmenen jeden radek, pak potvrzeni transakce
            //2) bylo zmeneno vice a nebo zadny, pak vratit transakci zpet
            if(updated == 1){
                con.commit(); //potvrzeni transakce
                ok = true;
            }else{
                con.rollback(); //vraceni transakce
                ok = false;
            }
        } catch (SQLException ex) {
            String s = ex.getMessage();
            throw new ErrorDatabase(ErrorDatabase.ERROR_DATABASE_CONECTION, "Cbyba pri praci s databazovym serverem.");
        } finally{
            try {
                stmt.close();
            } catch (SQLException ex) {}
        }
        return ok;
    }
    
    /**
     * Podle zadaneho id zajimavosti, vrati obek typu Bod_zaujmu
     */
    public Bod_zaujmu getInterest(String id) throws ErrorDatabase{
    	Bod_zaujmu score = null;
    	ResultSet rs = null;
        
        try {
            databaseConnect();
            //SELECT * FROM bod_zaujmu WHERE id = 1;
            rs = stmt.executeQuery("SELECT * FROM " + TABLE + " where " + ID + "='" + id + "';");
            con.commit();
            String s;
            while(rs.next()){
                score = new Bod_zaujmu();
                s = rs.getString(ID);
                score.setId(s);
                s = rs.getString(NAME);
                score.setNazov(s);
                s = rs.getString(SHORT_DESC);
                score.setKratkyPopis(s);
                s = rs.getString(LONG_DESC);
                score.setDlhyPopis(s);
                s = rs.getString(ADD_TIME);
                score.setPridane(s);
                s = rs.getString(LAST_EDIT_TIME);
                score.setNaposledyEditovane(s);
                s = rs.getString(CITY_ID);
                score.setMesto(s);
                s = rs.getString(EMPLOYEE_ID);
                score.setZamestnanec(s);
                s = rs.getString(GPS);
                score.setGps(s);
            }
        } catch (SQLException ex) {
        	throw new ErrorDatabase(ErrorDatabase.ERROR_DATABASE_CONECTION, "Cbyba pri praci s databazovym serverem.");
        } finally{
            try {
                rs.close();
                stmt.close();
            } catch (Exception ex) {}
        }
    	
    	return score;
    }
    
}

